﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.OracleClient;
using System.Data.Common;


/*
 * 访问 SQL SERVER 或者 Oracle 通用类
 * 与数据交互的对象全部在 System.Data.Common 中
 * 该类唯一的变化部分就是构造函数
 * 构造函数根据枚举中的枚举项来初始化相应的ADO对象
 */

namespace PCC.Framework.Data
{

    public enum DatabaseType
    {
        SQLSERVER,
        ORACLE
    }

    public class DBHelper
    {
        #region 类成员
        private string connectionString;
        private DbConnection iconn;
        private DbCommand icommand;
        private DbDataAdapter ida;
        private DbParameter iparam;
        private DbCommandBuilder builder;
        #endregion

        #region 构造函数

        /// <summary>
        /// 根据传入的数据库类型和字符串名称初始化数据库对象
        /// </summary>
        /// <param name="dbtype">数据库类型---枚举</param>
        /// <param name="connectionString">web.config文件的 connectionstring 节点中的节点名</param>
        public DBHelper(DatabaseType dbtype, string connectionString)
        {
            this.connectionString = connectionString;
            switch (dbtype)
            {
                case DatabaseType.ORACLE:
                    {
                        this.iconn = new OracleConnection(this.connectionString);
                        this.icommand = new OracleCommand( );
                        this.icommand.Connection = this.iconn;
                        this.ida = new OracleDataAdapter( );
                        iparam = new OracleParameter( );
                        this.builder = new OracleCommandBuilder( );
                        break;
                    }
                case DatabaseType.SQLSERVER:
                    {
                        this.iconn = new SqlConnection(this.connectionString);
                        this.icommand = new SqlCommand( );
                        this.icommand.Connection = this.iconn;
                        this.ida = new SqlDataAdapter( );
                        iparam = new SqlParameter( );
                        this.builder = new SqlCommandBuilder( );
                        break;
                    }
            }
        }

        /// <summary>
        /// 默认为 SQL SERVER 数据库
        /// </summary>
        /// <param name="connNodeName">config文件中 connectionstring节点名称</param>
        public DBHelper( string connectionString )
        {
            this.connectionString = connectionString;
            this.iconn = new SqlConnection(this.connectionString);
            this.icommand = new SqlCommand( );
            this.icommand.Connection = this.iconn;
            this.ida = new SqlDataAdapter( );
            this.iparam = new SqlParameter( );
            this.builder = new SqlCommandBuilder( );
        }

        #endregion

        #region DataAccess 成员函数

        public System.Data.DataTable GetTable(string selectString)
        {
            this.prepareCommand( );
            this.icommand.CommandText = selectString;
            ida.SelectCommand = this.icommand;
            DataSet ds = new DataSet( );
            ida.Fill(ds);
            return ds.Tables[0];
        }

        public object ExecuteScalar(string selectString)
        {
            this.prepareCommand( );
            this.icommand.CommandText = selectString;
            object o = this.icommand.ExecuteScalar( );
            this.icommand.Connection.Close( );
            return o;
        }

        public int ExecuteNonQuery(string updateString)
        {
            this.prepareCommand( );
            this.icommand.CommandText = updateString;
            int ret = this.icommand.ExecuteNonQuery( );
            this.icommand.Connection.Close( );
            return ret;
        }

        public int UpdateTable(DataTable dt, string selectString)
        {
            icommand.CommandText = selectString;
            ida.SelectCommand = this.icommand;
            this.builder.RefreshSchema( );
            this.builder.DataAdapter = ida;
            return ida.Update(dt);
        }

        /// <summary>
        /// 更新表数据
        /// 2010-11-15 18:41:51
        /// </summary>
        /// <param name="dataset"></param>
        /// <returns></returns>
        public int UpdateDataset(DataSet dataset)
        {
            //da.FillSchema(dataset, SchemaType.Mapped); //获取 dataset 中的 datatable 的表结构
            //SqlCommandBuilder builder = new SqlCommandBuilder();
            //builder.DataAdapter = da;
            //return da.Update(dataset);

            this.builder.RefreshSchema();
            this.ida.FillSchema(dataset, SchemaType.Mapped); //获取 dataset 中的 datatable 的表结构，供 dataAdapter 自动生成相应的 update 语句
            this.builder.DataAdapter = ida;
            return ida.Update(dataset);
        }

        public DataTable ExecuteProcRetTable(string procName, DbParameter[] Params)
        {
            this.icommand.CommandType = CommandType.StoredProcedure;
            this.icommand.Parameters.Clear( );
            this.icommand.CommandText = procName;
            foreach (DbParameter param in Params)
            {
                this.icommand.Parameters.Add(param);
            }
            DataSet ds = new DataSet( );
            ida.SelectCommand = this.icommand;
            ida.Fill(ds);
            return ds.Tables[0];
        }

        public object ExecuteProcRetObject(string procName, string retParameter, DbParameter[] Params)
        {
            this.PrepareProcCommand( );
            this.icommand.CommandText = procName;
            foreach (DbParameter param in Params)
            {
                this.icommand.Parameters.Add(param);
            }
            this.icommand.ExecuteNonQuery( );

            System.Type t = this.icommand.GetType( );
            switch (t.Name)
            {
                case "OracleCommand":
                    {
                        this.iparam = this.icommand.Parameters[retParameter] as OracleParameter;
                        break;
                    }
                case "SqlCommand":
                    {
                        this.iparam = this.icommand.Parameters[retParameter] as SqlParameter;
                        break;
                    }
            }

            this.icommand.Connection.Close( );
            return iparam.Value;
        }

        public void ExecuteProcNonRet(string procName, DbParameter[] Params)
        {
            this.PrepareProcCommand( );
            this.icommand.CommandText = procName;
            foreach (DbParameter param in Params)
            {
                this.icommand.Parameters.Add(param);
            }
            this.icommand.ExecuteNonQuery( );
            this.icommand.Connection.Close( );
        }

        #endregion

        #region 设置 Command 的相关属性

        /// <summary>
        /// prepare command for SQL-Query
        /// </summary>
        private void prepareCommand( )
        {
            if (this.icommand.Connection.State == ConnectionState.Closed)
            {
                this.icommand.Connection.Open( );
            }
            this.icommand.Parameters.Clear( );
            this.icommand.CommandType = CommandType.Text;
        }

        /// <summary>
        ///  prepare command for Procedure
        /// </summary>
        private void PrepareProcCommand( )
        {
            if (this.icommand.Connection.State == ConnectionState.Closed)
            {
                this.icommand.Connection.Open( );
            }
            this.icommand.Parameters.Clear( );
            this.icommand.CommandType = CommandType.StoredProcedure;
        }

        #endregion
    }
}
